
/**
 * Written for HIT3119 Assignment 1 (Bank System).<br/> Creates database tables
 * for Bank System.<br/><br/>
 *
 * @author (c) Alexis Wellavize (s/n: 6033482) Craig Parsons (s/n: 6955657) Mark
 * Isherwood (s/n: 5571340) Dotan Tamir (s/n: 6532969)
 * @version 0.1 - Modified 05/04/2012
 * @since 0.1 - Created 05/04/2012 @note v0.1 - 05/04/2012 - First release.
 */
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 * @brief Class for ancillary database functions.
 */
public class DB {

    /**
     * @brief Creates the connection to the database.
     *
     * @return Connection to the database
     * @throws SQLException
     * @throws IOException
     */
    public static Connection getConnection()
            throws SQLException, IOException {
        System.setProperty("jdbc.drivers", "org.apache.derby.jdbc.ClientDriver");
        String url = "jdbc:derby://localhost/sun-appserv-samples;create=true";
        String username = "APP";
        String password = "APP";

        return DriverManager.getConnection(url, username, password);
    }

    public void executeSQLScript(String scriptLocation, boolean verbose) {
        Connection cnnct = null;
        Statement stmnt = null;
        try {
            cnnct = getConnection();
            stmnt = cnnct.createStatement();

            BufferedReader in = new BufferedReader(new FileReader(scriptLocation));
            String str;
            StringBuilder sb = new StringBuilder();

            while ((str = in.readLine()) != null) {

                str = str.replaceAll("\\-{2,}.*", "");
                sb.append(str).append("\n ");
            }
            in.close();

            String[] stringArr = sb.toString().split(";");

            for (String token : stringArr) {
                try {
                    if (verbose) {
                        System.out.println("#Command#" + token);
                    }
                    stmnt.execute(token);
                } catch (Exception e) {
                    if (verbose) {
                        System.err.println("Error: " + e.getMessage());
                    }
                }
            }
        } catch (SQLException ex) {
            while (ex != null) {
                ex.printStackTrace();
                ex = ex.getNextException();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            if (stmnt != null) {
                try {
                    stmnt.close();
                } catch (SQLException e) {
                }
            }
            if (cnnct != null) {
                try {
                    cnnct.close();
                } catch (SQLException sqlEx) {
                }
            }
        }
    }

    public static void main(String args[]) {
        DB db = new DB();
        while (true) {
            System.out.println("1: Drop Tables");
            System.out.println("2: Refresh Database Schema");
            System.out.println("3: Insert Dummy Data");
            System.out.println("4: Drop, Refresh and Insert");
            System.out.println("5: Exit");
            System.out.print(":");
            Scanner scan = new Scanner(System.in);
            String input = scan.nextLine();

            if (input.equalsIgnoreCase("1")) {
                db.executeSQLScript("scripts/dropTables.sql", false);
            } else if (input.equalsIgnoreCase("2")) {
                db.executeSQLScript("scripts/create.sql", false);
            } else if (input.equalsIgnoreCase("3")) {
                db.executeSQLScript("scripts/insert.sql", false);
            } else if (input.equalsIgnoreCase("4")) {
                db.executeSQLScript("scripts/dropTables.sql", false);
                db.executeSQLScript("scripts/create.sql", false);
                db.executeSQLScript("scripts/insert.sql", false);
                break;
            } else if (input.equalsIgnoreCase("5")) {
                break;
            }
        }
    }
}
